package org.usts.trainning.supermarket.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.usts.trainning.supermarket.entity.Provider;
import org.usts.trainning.supermarket.util.DBUtilities;

public class ProviderDao {
	final String configFile = "jdbc.properties";
	// 配置文件
	private Connection conn = null;
	String tableName = null;

	/** * 获得数据库连接 */
	private Connection getConnection() {
		if (conn == null) {
			String strPath = this.getClass().getResource("/jdbc.properties").getPath();
			conn = DBUtilities.connect(strPath);
		}
		return conn;
	}

	/** * @param */
	public ProviderDao() {
		getConnection();
		if (conn == null) {
			try {
				throw new SQLException("无法连接数据库！");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// 查看所有供应商信息
	public List<Provider> findAllProvider() {
		// TODO Auto-generated method stub
		List<Provider> list = null;
		String[] params = null;
		tableName = "provider";
		// 1.判断数据库的连接是否已经获取
		if (conn != null) {
			// 2.准备 SQL 语句
			String sql = "Select * From " + tableName;
			params = new String[] {};
			ResultSet rs = null;
			try {
				// 3.执行 SQL 语句
				rs = DBUtilities.querySQL(conn, sql, params); // 4.处理查询结果
				if (rs != null) {
					list = new ArrayList<Provider>();
					while (rs.next()) {
						Provider provider = new Provider();
						provider.setPid(rs.getInt(1));
						provider.setPcode(rs.getString(2));
						provider.setPsupplier(rs.getString(3));
						provider.setContact(rs.getString(4));
						provider.setTel(rs.getString(5));
						provider.setFax(rs.getString(6));
						provider.setPtime(rs.getString(7));
						provider.setPdesc(rs.getString(8));
						list.add(provider);

					}
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					rs.close();
				} catch (SQLException e) {
				}
			}
		}
		return list;
	}

	// 根据用户名查找
	public Provider findProById(Integer pid) {
		Provider provider = null;
		String[] params = null;
		tableName = "provider";
		// 1.判断数据库的连接是否已经获取
		if (conn != null) {
			// 2.准备 SQL 语句
			String sql = "Select * From " + tableName + " where pid = ? ";
			params = new String[] { String.valueOf(pid) };
			ResultSet rs = null;
			try {
				// 3.执行 SQL 语句
				rs = DBUtilities.querySQL_2(conn, sql, params);
				// 4.处理查询结果
				if (rs != null) {
					if (rs.next()) {
						provider = new Provider();
						provider.setPid(rs.getInt(1));
						provider.setPcode(rs.getString(2));
						provider.setPsupplier(rs.getString(3));
						provider.setContact(rs.getString(4));
						provider.setTel(rs.getString(5));
						provider.setFax(rs.getString(6));
						provider.setPtime(rs.getString(7));
						provider.setPdesc(rs.getString(8));

					}
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					rs.close();
				} catch (SQLException e) {
				}
			}
		}
		return provider;
	}

	// 修改provider
	public Boolean updateProById(Provider provider) {
		boolean result = false;
		String[] params = null;
		tableName = "provider";
		// 1.判断数据库的连接是否已经获取
		if (conn != null) {
			// 2.准备 SQL 语句，及参数
			String sql = "update " + tableName
					+ " set pcode=?,psupplier=?,contact=?,tel=?,fax=?,ptime=?,pdesc=? where pid=?";

			params = new String[] { provider.getPcode(), provider.getPsupplier(), provider.getContact(),
					provider.getTel(), provider.getFax(), provider.getPtime(), provider.getPdesc(),
					String.valueOf(provider.getPid()) };
			// 3.执行查询的 SQL语句
			result = DBUtilities.updateSQL(conn, sql, params);
		}
		return result;
	}

	// 根据id删除用户
	public Boolean deletePro(Integer pid) {
		boolean result = false;
		String[] params = null;
		tableName = "provider";
		// 1.判断数据库的连接是否已经获取
		if (conn != null) {
			// 2.准备 SQL 语句，及参数
			String sql = "DELETE FROM " + tableName + " where pid=?";
			params = new String[] { String.valueOf(pid) };
			// 3.执行查询的 SQL语句
			result = DBUtilities.updateSQL(conn, sql, params);
		}
		return result;
	}

	// 增加账单
	public Boolean addPro(Provider provider) {
		boolean result = false;
		String[] params = null;
		tableName = "provider";
		// 1.判断数据库的连接是否已经获取
		if (conn != null) {
			// 2.准备 SQL 语句，及参数
			String sql = "Insert Into " + tableName
					+ " (pcode,psupplier,contact,tel,fax,ptime,pdesc) value(?,?,?,?,?,?,?)";
			params = new String[] { provider.getPcode(), provider.getPsupplier(), provider.getContact(),
					provider.getTel(), provider.getFax(), provider.getPtime(), provider.getPdesc() };
			// 3.执行查询的 SQL语句
			result = DBUtilities.updateSQL(conn, sql, params);
		}
		return result;
	}

	// 根据供应商名称查找
	public List<Provider> findAllProvider(String psupplier) {
		// TODO Auto-generated method stub
		List<Provider> list = null;
		String[] params = null;
		tableName = "provider";
		// 1.判断数据库的连接是否已经获取
		if (conn != null) {
			// 2.准备 SQL 语句
			String sql = "Select * From " + tableName + " where psupplier=?";
			params = new String[] { psupplier };
			ResultSet rs = null;
			try {
				// 3.执行 SQL 语句
				rs = DBUtilities.querySQL_2(conn, sql, params); // 4.处理查询结果
				if (rs != null) {
					list = new ArrayList<Provider>();
					while (rs.next()) {
						Provider provider = new Provider();
						provider.setPid(rs.getInt(1));
						provider.setPcode(rs.getString(2));
						provider.setPsupplier(rs.getString(3));
						provider.setContact(rs.getString(4));
						provider.setTel(rs.getString(5));
						provider.setFax(rs.getString(6));
						provider.setPtime(rs.getString(7));
						provider.setPdesc(rs.getString(8));
						list.add(provider);

					}
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					rs.close();
				} catch (SQLException e) {
				}
			}
		}
		return list;
	}

	// 关闭数据库连接
	public void close() {
		try {
			if (conn != null)
				conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
}
